﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DB_test
{
    
    public static class MyDbConnection
    {
        private static SqlConnection sqlConnection;

        public static void SetConnectionString(string connectionString)
        {
            sqlConnection = new SqlConnection(connectionString);
        }

        public static int ExecuteInsert(string commandString)
        {
            if (sqlConnection.State != ConnectionState.Open)
                        sqlConnection.Open();

            int returnValue = -1;
            using (var command = new SqlCommand(commandString, sqlConnection))
            {
                returnValue = (int)command.ExecuteScalar();
            }
            sqlConnection.Close();
            return returnValue;
        }

        public static void ExecuteUpdate(string commandString)
        {
            if (sqlConnection.State != ConnectionState.Open)
                sqlConnection.Open();

            using (var command = new SqlCommand(commandString, sqlConnection))
            {
                command.ExecuteScalar();
            }
            sqlConnection.Close();
        }

        public static List<Dictionary<String, Object>> ReadFromDataBase(string selectString)
        {
            List<Dictionary<String, Object>> objectsInQuery = null;

            if (sqlConnection.State != ConnectionState.Open)
                        sqlConnection.Open();

            using (var command = new SqlCommand(selectString, sqlConnection))
            {
                objectsInQuery = new List<Dictionary<String, Object>>();

                var reader = command.ExecuteReader();
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        var dictionaryInRow = new Dictionary<String, Object>();
                        for (int fieldIndex = 0; fieldIndex < reader.FieldCount; fieldIndex++)
                        {
                            dictionaryInRow.Add(reader.GetName(fieldIndex), reader.GetValue(fieldIndex));
                        }
                        objectsInQuery.Add(dictionaryInRow);
                    }
                }
                else
                {

                }
                reader.Close();
                sqlConnection.Close();
            }

            return objectsInQuery;
        }
    }
}
